﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using QLZJFurnitureSCM.Model;
using System.Data.Linq;
using System.Data.SqlClient;
using System.Data;

namespace QLZJFurnitureSCM.DAL
{
    public class FurnitureTypeDAO : IBaseDAO<FurnitureType>, IFurnitureTypeDAO
    {
        private string connectionString = DALFactory.CONNECTIONSTRING;

        public FurnitureType Create(FurnitureType t)
        {
            SqlConnection sqlConnection = new SqlConnection(connectionString);
            string query = string.Format("insert into FurnitureType(typeName,typeUpId) values('{0}',{1});Select @@identity;", t.TypeName, t.TypeUpId);
            SqlCommand sqlCmd = new SqlCommand(query, sqlConnection);
            if (sqlConnection.State != ConnectionState.Open)
                sqlConnection.Open();
            try
            {
                object o = sqlCmd.ExecuteScalar();
                if (o != null)
                    t.TypeId = int.Parse(o.ToString());
                Console.WriteLine(query);
                sqlConnection.Close();
            }
            catch (System.Exception ex)
            {
                Console.WriteLine(ex.Message);
                return Create(t);
            }
           
            return t;
            /*
            DataContext dataContext = new DataContext(connectionString);
            try
            {
                string query = string.Format("insert into FurnitureType(typeName,typeUpId) values('{0}',{1});", t.TypeUpId, t.TypeName);
                Console.WriteLine(query);
                IEnumerable<FurnitureType> enumFurnitureType = dataContext.<FurnitureType>(query);
                foreach (FurnitureType furnitureType in enumFurnitureType.ToList())
                {
                    Console.WriteLine(furnitureType.TypeId + ":" + furnitureType.TypeName + ":" + furnitureType.TypeUpId);
                    type = furnitureType;
                }
                
            }
            catch (System.Exception)
            {
                //return false;
            }*/
        }

        public FurnitureType Read(FurnitureType t)
        {
            throw new NotImplementedException();
        }

        public bool Update(FurnitureType t)
        {
            DataContext dataContext = new DataContext(connectionString);
            try
            {
                dataContext.ExecuteQuery<FurnitureType>(string.Format("Update FurnitureType set typeName = '{0}' where typeId = {1};", t.TypeName, t.TypeId));
                dataContext.Dispose();
            }
            catch (System.Exception)
            {
                return false;
            }
            return true;
        }

        public bool Delete(FurnitureType t)
        {
            DataContext dataContext = new DataContext(connectionString);
            try
            {
                string query = string.Format("Delete from FurnitureType where typeId = {0};", t.TypeId);
                Console.WriteLine("delete query:" + query);
                dataContext.ExecuteQuery<FurnitureType>(query);
            }
            catch (System.Exception)
            {
                return false;
            }
            return true;
        }

        public List<FurnitureType> ReadAll()
        {
            DataContext dataContext = new DataContext(connectionString);
            IEnumerable<FurnitureType> enumFurniture = null;
            dataContext.CommandTimeout = 60;
            try
            {
                enumFurniture = dataContext.ExecuteQuery<FurnitureType>("select * from FurnitureType order by typeId;");

            }
            catch (System.Data.SqlClient.SqlException)
            {
                return null;
            }
            return enumFurniture.ToList();
        }

        public List<FurnitureType> FindFurnitureSubTypeByTypeUpId(int typeUpId)
        {
            DataContext dataContext = new DataContext(connectionString);
            IEnumerable<FurnitureType> enumFurniture = dataContext.ExecuteQuery<FurnitureType>(string.Format("select * from FurnitureType where typeId = '{0}';", typeUpId));
            return enumFurniture.ToList();
        }


        public List<FurnitureType> FindFurnitureUpTypeByTypeId(int typeId)
        {
            DataContext dataContext = new DataContext(connectionString);
            IEnumerable<FurnitureType> enumFurniture = dataContext.ExecuteQuery<FurnitureType>(string.Format("select * from FurnitureType where typeUpId = '{0}';", typeId));
            return enumFurniture.ToList();
        }


        public bool UpdateFurnitureUpType(int typeId, int typeUpId)
        {
            DataContext dataContext = new DataContext(connectionString);
            try
            {
                dataContext.ExecuteQuery<FurnitureType>(string.Format("update FurnitureType set typeUpId = '{0}' where typeId = {1};", typeUpId, typeId));
            }
            catch (System.Exception)
            {
                return false;
            }
            return true;
        }
    }
}
